package org.example.entity;


import org.hibernate.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.*;
import org.hibernate.jdbc.Work;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.hibernate.type.StandardBasicTypes;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;


/**
 * @author zhaokuii11@163.com
 * @create 2021-12-06 18:37
 * @Description
 */
public class Test1 {
    private SessionFactory sessionFactory;//会话工厂
    private Session session;//会话
    private Transaction transaction;//事务


    @Before //juint测试方法 之前执行
    public void init() {
        System.out.println("init");
        //1. 根据 Configuration创建 会话工厂
        sessionFactory = new Configuration().configure().buildSessionFactory();
        //2. 创建 会话
        session = sessionFactory.openSession();
        //3. 创建事务
        transaction = session.beginTransaction();
    }

    @Test
    public void testHibernateSecodLevelCache() {
        //发送了一条 sql
        Employee employee1 = session.get(Employee.class, 100);
//        System.out.println(employee1.getName());

        Employee employee2 = this.session.get(Employee.class, 100);
//        System.out.println(employee2.getName());

    }


    @Test
    public void testHQLUpdtae() {
        String hql = "delete from Department d where d.id =:id";
        session.createQuery(hql).setParameter("id", 2).executeUpdate();
    }

    @Test
    public void testNativeSQL() {
        String sql = "insert into tab_dept values(?,?)";
        NativeQuery nativeQuery = session.createSQLQuery(sql.toString());
        //下标从1开始
        nativeQuery.setParameter(1, null, StandardBasicTypes.INTEGER);
        nativeQuery.setParameter(2, "ps", StandardBasicTypes.STRING);
        nativeQuery.executeUpdate();
    }

    @Test
    public void testQBC4() {
        Criteria criteria = session.createCriteria(Employee.class);
        //1. 添加排序查询
        criteria.addOrder(Order.asc("salary"));
        criteria.addOrder(Order.desc("email"));
        //2. 添加翻页方法
        int pageSize = 5;
        int pageNo = 3;
        List list = criteria.setFirstResult(0)
                .setMaxResults(pageSize).list();
        System.out.println(list);
    }

    @Test
    public void testQBC3() {
        Criteria criteria = session.createCriteria(Employee.class);
        //统计查询；使用 Projection表示,可以由 Projections的静态方法得到
        criteria.setProjection(Projections.max("salary"));
        System.out.println(criteria.uniqueResult());
    }

    @Test
    public void testQBC2() {
        //1. and :使用 Conjunction对象
        // Conjunction 本身就是一个 Criterion对象
        //且其中还可以添加 Criterion对象
        Conjunction conjunction = Restrictions.conjunction();
        conjunction.add(Restrictions.like("name", MatchMode.ANYWHERE));
        Department dept = new Department();
        dept.setId(1);
        conjunction.add(Restrictions.eq("dept", dept));
        System.out.println(conjunction);
        //(name like ANYWHERE and dept=Department{id=1, name='null', emps=[]})
        //2. or
        Disjunction disjunction = Restrictions.disjunction();
        disjunction.add(Restrictions.ge("salary", 6000F));
        disjunction.add(Restrictions.isNull("email"));
        //(salary>=6000.0 or email is null)
        System.out.println(disjunction);
    }

    @Test
    public void testQBC() {
        //1. 创建 Criteria 对象
        Criteria criteria = session.createCriteria(Employee.class);
        //2. 添加查询条件:在 qbc中查询条件使用 criterion来表示
        //Criteria 可以通过 Restrictions的静态方法得到
        criteria.add(Restrictions.eq("email", "aa@123.com"));
        criteria.add(Restrictions.gt("salary", 30F));
        //3. 执行查询
        Employee employee = (Employee) criteria.uniqueResult();
        System.out.println(employee);
    }

    @Test
    public void testLeftJoin() {
        String hql = "from Department d left join d.emps";
        Query query = session.createQuery(hql);
        List<Object[]> list = query.list();
        for (Object[] objects : list) {
            System.out.println(Arrays.asList(objects));
        }
    }

    @Test
    public void testLeftJoinFetch() {
//        String hql = "select distinct d from Department d left join fetch d.emps";
        String hql = "from Department d left join fetch d.emps";
        Query query = session.createQuery(hql);
        List list = query.list();
        System.out.println(list);
    }

    @Test
    public void testGroupBy() {
        String hql = "select min(e.salary),max(e.salary) from Employee e group by e.dept having  min(salary) > :minSal";
        Query query = session.createQuery(hql);
        query.setParameter("minSal", 5000F);
        List<Object[]> list = query.list();
        for (Object i : list) {
            System.out.println(Arrays.asList(i));
        }
    }

    @Test
    public void testFieldQuery2() {
        //投影查询 需要提供该构造方法
        String hql = "select new Employee(e.email,e.salary,e.dept) from Employee e where e.dept= :dept";
        Query query = session.createQuery(hql);
        Department department = new Department();
        department.setId(1);
        List<Employee> result = query.setParameter("dept", department).list();

        System.out.println(result.getClass());
//        result.forEach(System.out::println);
    }

    @Test
    public void testFieldQuery() {
        //投影查询 字段
        String hql = "select e.email,e.salary,e.dept from Employee e where e.dept= :dept";
        Query query = session.createQuery(hql);
        Department department = new Department();
        department.setId(1);
        List<Object[]> result = query.setParameter("dept", department).list();

        System.out.println(result.getClass());
        result.forEach(e -> {
            System.out.println(Arrays.toString(e));
        });
    }

    @Test
    public void testNameQuery() {
        //获取 query 对象
        Query query = session.getNamedQuery("salaryEmps");
        //设置参数
        query.setParameter("minSal", 5000F);
        query.setParameter("maxSal", 10000F);
        //获取结果
        List<Employee> list = query.list();
        System.out.println(list);
    }

    @Test
    public void testPageQuery() {
        //分页
        String hql = "from Employee";
        Query query = session.createQuery(hql);
        int pageNo = 3;
        int pageSize = 5;
        //limit setFirstResult?,setMaxResults?
        List list = query.setFirstResult((pageNo - 1) * pageSize)//第几条开始
                .setMaxResults(pageSize) //设置记录显示条目数
                .list();
        System.out.println(list);
    }

    @Test
    public void testHQL() {
        //1. 创建 query对象
        //基于位置的参数
        String hql = "from Employee where id=2 group by salary";
        Query query = session.createQuery(hql);
        //Query 对象调用 setXxx方法支持的方法链编程风格
        //3. 执行查询
        List list = query.list();
        System.out.println(list);
    }

    @After//juint测试方法 之后执行
    public void destroy() {
        //先开后关
        System.out.println("destroy");
        transaction.commit();
        session.close();
        sessionFactory.close();
    }
}
